package es.cea;

import java.sql.Connection;

import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.testng.annotations.BeforeTest;
import org.testng.annotations.Test;

public class TestLibreria {

	@BeforeTest
	public void insertarDatosAutores(){
		
		String nombres[] = new String [5];
		int id_Autor[] = {001,002,003,004,005};
		String Nombre[] = {"Juan", "Pedro","Manuel","Rosa","Cristina"};
		String FechaNac[] = {"1982/08/09", "1990/02/02", "1992/11/09", "1980/11/10", "1991/11/10"};
		
		
		
		try  
		{  
		Class.forName("com.mysql.jdbc.Driver");  
		Connection conexion = DriverManager.getConnection ("jdbc:mysql://localhost/libreria","root", "root");  
		Statement s = conexion.createStatement();  
		
	
		  s.executeUpdate("delete from Autores");

		for (int i=0;i<nombres.length;i++)
			
			  s.executeUpdate("INSERT INTO Autores (id_Autor, Nombre, Fecha_Nac) VALUES ('"+id_Autor[i]+"','"+Nombre[i]+"','"+FechaNac[i]+"' )");
	
		
		} catch (Exception e)  
		{  
		   e.printStackTrace();  
		}  
}
	
	public class TestLibreria2 {
		
		@Test
		public void insertarDatosLibros(){
			
			String nombres[] = new String [5];
			int id_Libro[] = {001,002,003,004,005};
			String titulo[] = {"Poesia", "La casa de...","Don quijote...","Crimen y...","Invictus"};
			String fecha[] = {"1982/08/09", "1990/02/02", "1992/11/09", "1980/11/10", "1991/11/10"};
			int idAutor[] = {001,002,003,004,005};
			
			
			
			try  
			{  
			Class.forName("com.mysql.jdbc.Driver");  
			Connection conexion = DriverManager.getConnection ("jdbc:mysql://localhost/libreria","root", "root");  
			Statement s = conexion.createStatement();  
			
		
			 s.executeUpdate("delete from Libros");
			
			for (int i=0;i<nombres.length;i++)
				
				  s.executeUpdate("INSERT INTO Libros (id_Libro, titulo, fecha, idAutor) VALUES ('"+id_Libro[i]+"','"+titulo[i]+"','"+fecha[i]+"','"+idAutor[i]+"' )");
		
			
			} catch (Exception e)  
			{  
			   e.printStackTrace();  
			}  
	}

	}


	@Test
	public void convertirObjAutor() {
		
		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conexion = DriverManager.getConnection(
					"jdbc:mysql://localhost/libreria", "root", "root");
			Statement s = conexion.createStatement();
			ResultSet rs = s.executeQuery("select * from autores");
			List autores = new ArrayList();
			while (rs.next()) {
				System.out.println(rs.getInt(1) + " " + rs.getString(2) + "  "
						+ rs.getDate(3));

				AutorJDBC autor = new AutorJDBC();

				autor.id = rs.getInt(1);
				autor.nombre = rs.getString(2);
				autor.FechaNac = rs.getDate(3);

				autores.add(autor);

				System.out.println(autor.toString());

			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
	}
	@Test
	public void convertirObjLibro() {

		try {
			Class.forName("com.mysql.jdbc.Driver");
			Connection conexion = DriverManager.getConnection(
					"jdbc:mysql://localhost/libreria", "root", "root");
			Statement s = conexion.createStatement();
			ResultSet rs = s.executeQuery("select * from libros");
			List librosList = new ArrayList();

			while (rs.next()) {
//				System.out.println(rs.getInt(1) + " " + rs.getString(2) + "  "
//						+ rs.getDate(3) + "  " + rs.getString(4));

				LibroJDBC libro = new LibroJDBC();
				AutorJDBC autor = new AutorJDBC();

				libro.idLibro = rs.getInt(1);
				libro.titulo = rs.getString(2);
				libro.fecha = rs.getDate(3);
				
				autor.setId(rs.getInt(1));
				
				
				librosList.add(libro);
				
				String salida = libro.toString();
				System.out.println(salida);

			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		

	}
	
}



